package cn.topevery.garden.dal.dao.impl;

import cn.topevery.garden.dal.dao.EvaluationRulesDao;
import cn.topevery.garden.entity.po.EvaluationRules;
import cn.topevery.garden.entity.query.EvaluationRulesQuery;
import cn.topevery.garden.entity.vo.EvaluationRulesVO;
import cn.topevery.garden.entity.vo.EvaluationTableVO;
import cn.topevery.garden.entity.vo.EvaluationTreeVO;
import cn.topevery.garden.framework.base.BaseDaoImpl;
import hw.topevery.framework.db.CommandType;
import hw.topevery.framework.db.entity.DataPager;
import hw.topevery.framework.db.entity.SqlQueryMate;
import hw.topevery.framework.db.enums.ScriptConditionEnum;
import hw.topevery.framework.entity.DbCsp;
import hw.topevery.framework.entity.KeyValue;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.concurrent.atomic.AtomicReference;

/**
 * 检查考评规则表
 *
 * @author code-generate
 * @date 2022-01-05
 */
@Repository
public class EvaluationRulesDaoImpl extends BaseDaoImpl<EvaluationRules> implements EvaluationRulesDao {
    @Override
    public <P extends DbCsp> List<EvaluationRules> getSearch(P para) {
        return null;
    }

    

    @Override
    public DataPager<EvaluationRulesVO> listPage(EvaluationRulesQuery query) {
        String table = "t_evaluation_rules ter";

        String columns = "ter.*";

        int pageIndex = query.pageIndex - 1;
        int pageSize = query.pageSize;

        SqlQueryMate sqlQueryMate = new SqlQueryMate(table).setColumns(columns).setOrderBy("ter.c_id desc");
        sqlQueryMate
                .where(true, "ter.c_db_status", ScriptConditionEnum.Eq, 0)
        ;

        return getDataPager(sqlQueryMate, pageSize, pageIndex, EvaluationRulesVO.class);
    }

    @Override
    public EvaluationRules getEntityByColumn(String key,String value){
        KeyValue<String> map=new KeyValue<>();
        map.key=key;
        map.value=value;
        return getEntityByColumnEqValue(map);
    }

    @Override
    public List<EvaluationRules> getRootList() {
        StringBuffer sb = new StringBuffer();
        sb.append("select * from t_evaluation_rules where c_db_status = 0 and  c_layer = 0");
        AtomicReference<List<EvaluationRules>> vo = new AtomicReference<>(new ArrayList<>());
        run(sb.toString(), CommandType.Text, dbCommand -> {
            vo.set(dbCommand.executeToList(EvaluationRules.class));
        });
        return vo.get();
    }

    @Override
    public List<EvaluationRules> getListByPid(String pid) {
        StringBuffer sb = new StringBuffer();
        sb.append("select * from t_evaluation_rules where c_db_status = 0 and  c_rule_pid = '"+pid+"'");
        AtomicReference<List<EvaluationRules>> vo = new AtomicReference<>(new ArrayList<>());
        run(sb.toString(), CommandType.Text, dbCommand -> {
            vo.set(dbCommand.executeToList(EvaluationRules.class));
        });
        return vo.get();
    }


    @Override
    public void deleteByRuleId(String ruleId, Integer layer, Integer userId) {

        String sql = "update t_evaluation_rules rule\n" +
                "        set c_db_status = 1"+
                "        where rule.c_rule_id ='"+ruleId+"'";
        run(sql, CommandType.Text, dbCommand -> {
            dbCommand.executeNonQuery();
        });
    }

    @Override
    public EvaluationRules getByRuleId(String ruleId) {
        StringBuffer sb = new StringBuffer();
        sb.append("select * from t_evaluation_rules where c_db_status = 0 and  c_rule_id = '"+ruleId+"'");
        AtomicReference<EvaluationRules> vo = new AtomicReference<>();
        run(sb.toString(), CommandType.Text, dbCommand -> {
            vo.set(dbCommand.executeToEntity(EvaluationRules.class));
        });
        return vo.get();
    }

    @Override
    public void updateItemScore(String ruleId, Double totalScore) {
        String sql = "update t_evaluation_rules rule\n" +
                "        set rule.c_item_score = rule.c_item_score"+ totalScore+
                "        where rule.c_rule_id ='"+ruleId+"'";
        run(sql, CommandType.Text, dbCommand -> {
            dbCommand.executeNonQuery();
        });
    }

    @Override
    public List<EvaluationTableVO> getEvaluationTable(Map<String, Object> map) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT tt.oneRuleId,\n" +
                "        tt.oneContent,\n" +
                "        tt.twoRuleId,\n" +
                "        tt.twoContent,\n" +
                "        tt.totalScore,\n" +
                "        tt.oneOrder,\n" +
                "        tt.twoOrder,\n" +
                "        t3.c_order_num as threeOrder,\n" +
                "        t3.c_rule_id threeRuleId,\n" +
                "        t3.c_content threeContent,\n" +
                "        t3.c_dock_score AS dockScore,\n" +
                "        t3.c_unit AS unit,\n" +
                "        IFNULL(ss.dockedScore, 0) dockedScore\n" +
                "        FROM (\n" +
                "        SELECT t1.c_rule_id AS oneRuleId,\n" +
                "        t1.c_content AS oneContent,\n" +
                "        t1.c_order_num as oneOrder,\n" +
                "        t2.c_order_num as twoOrder,\n" +
                "        t2.c_rule_id AS twoRuleId,\n" +
                "        t2.c_content AS twoContent,\n" +
                "        t2.c_total_score AS totalScore\n" +
                "        FROM t_evaluation_rules t1\n" +
                "        INNER JOIN t_evaluation_rules t2 ON t1.c_rule_id = t2.c_rule_pid\n" +
                "        AND t1.c_layer = 0\n" +
                "        AND t1.c_db_status = 0\n");
                if(map.get("ruleId").toString() != null && !map.get("ruleId").toString().isEmpty()){
                    sb.append(" AND t1.c_rule_id = '"+map.get("ruleId").toString()+"'");
                }
                sb.append(
                "        AND t2.c_db_status = 0\n" +
                "        ) tt\n" +
                "        INNER JOIN t_evaluation_rules t3 ON tt.twoRuleId = t3.c_rule_pid\n" +
                "        AND t3.c_db_status = 0\n" +
                "        LEFT JOIN (\n" +
                "        SELECT score.c_rule_id,\n" +
                "        score.c_dept_id,\n" +
                "        sum(IFNULL(score.c_dock_score, 0)) as dockedScore\n" +
                "        FROM t_evaluation_log score\n" +
                "        WHERE score.c_db_status = 0\n" +
                "        AND score.c_log_date >= '"+map.get("beginDate").toString()+"'" +
                "        AND score.c_log_date <= '"+map.get("endDate").toString()+"'" +
                "        GROUP BY score.c_rule_id,\n" +
                "        score.c_dept_id\n" +
                "        ) ss ON t3.c_rule_id = ss.c_rule_id\n" +
                "        AND ss.c_dept_id ='"+map.get("deptId").toString()+"'");

        AtomicReference<List<EvaluationTableVO>> vo = new AtomicReference<>(new ArrayList<>());
        run(sb.toString(), CommandType.Text,dbCommand -> {
            vo.set(dbCommand.executeToList(EvaluationTableVO.class));
        });

        return vo.get();
    }

    @Override
    public List<Map<String, Object>> getStatisticsTable(Map<String, Object> map) {
        return getMap(" SELECT dept.c_dept_id                                AS deptId,\n" +
                "               dept.c_dept_name                              AS deptName,\n" +
                "               100 - (\n" +
                "                   IFNULL(sum(ttt.dockedScore), 0))          AS score,\n" +
                "               sum(\n" +
                "                       CAST(\n" +
                "                               ttt.日常养护记录 AS DECIMAL(3, 1))) AS 日常养护记录,\n" +
                "               sum(\n" +
                "                       CAST(\n" +
                "                               ttt.安全生产 AS DECIMAL(3, 1)))   AS 安全生产,\n" +
                "               sum(\n" +
                "                       CAST(\n" +
                "                               ttt.卫生保洁 AS DECIMAL(3, 1)))   AS 卫生保洁,\n" +
                "               sum(\n" +
                "                       CAST(\n" +
                "                               ttt.园林管理 AS DECIMAL(3, 1)))   AS 园林管理,\n" +
                "               sum(\n" +
                "                       CAST(\n" +
                "                               ttt.绿化养护 AS DECIMAL(3, 1)))   AS 绿化养护,\n" +
                "               sum(\n" +
                "                       CAST(\n" +
                "                               ttt.日常施肥 AS DECIMAL(3, 1)))   AS 日常施肥,\n" +
                "               sum(\n" +
                "                       CAST(\n" +
                "                               ttt.日常巡查 AS DECIMAL(3, 1)))   AS 日常巡查,\n" +
                "               sum(\n" +
                "                       CAST(\n" +
                "                               ttt.侧重性检查 AS DECIMAL(3, 1)))  AS 侧重性检查,\n" +
                "               sum(\n" +
                "                       CAST(\n" +
                "                               ttt.其他 AS DECIMAL(3, 1)))     AS 其他\n" +
                "        FROM (\n" +
                "                 SELECT b.*\n" +
                "                 FROM t_sys_dept_in_tree a\n" +
                "                          JOIN t_sys_dept b ON a.c_dept_id = b.c_dept_id\n" +
                "                     AND a.c_db_status = 0\n" +
                "                     AND b.c_dept_type = '15003'\n" +
                "                 WHERE c_dept_tree_id = '55003f98-f416-4dbc-b049-513337bbc4c6'\n" +
                "             ) AS dept\n" +
                "                 LEFT JOIN (\n" +
                "            SELECT result.c_dept_id,\n" +
                "                   result.c_dept_name,\n" +
                "                   result.oneRuleId,\n" +
                "                   result.oneContent,\n" +
                "                   result.dockedScore,\n" +
                "                   CASE\n" +
                "                       oneContent\n" +
                "                       WHEN '日常养护记录' THEN\n" +
                "                           sum(result.score)\n" +
                "                       END AS 日常养护记录,\n" +
                "                   CASE\n" +
                "                       oneContent\n" +
                "                       WHEN '安全生产' THEN\n" +
                "                           sum(result.score)\n" +
                "                       END AS 安全生产,\n" +
                "                   CASE\n" +
                "                       oneContent\n" +
                "                       WHEN '卫生保洁' THEN\n" +
                "                           sum(result.score)\n" +
                "                       END AS 卫生保洁,\n" +
                "                   CASE\n" +
                "                       oneContent\n" +
                "                       WHEN '园林管理' THEN\n" +
                "                           sum(result.score)\n" +
                "                       END AS 园林管理,\n" +
                "                   CASE\n" +
                "                       oneContent\n" +
                "                       WHEN '绿化养护' THEN\n" +
                "                           sum(result.score)\n" +
                "                       END AS 绿化养护,\n" +
                "                   CASE\n" +
                "                       oneContent\n" +
                "                       WHEN '日常施肥' THEN\n" +
                "                           sum(result.score)\n" +
                "                       END AS 日常施肥,\n" +
                "                   CASE\n" +
                "                       oneContent\n" +
                "                       WHEN '日常巡查' THEN\n" +
                "                           sum(result.score)\n" +
                "                       END AS 日常巡查,\n" +
                "                   CASE\n" +
                "                       oneContent\n" +
                "                       WHEN '侧重性检查' THEN\n" +
                "                           sum(result.score)\n" +
                "                       END AS 侧重性检查,\n" +
                "                   CASE\n" +
                "                       oneContent\n" +
                "                       WHEN '其他' THEN\n" +
                "                           sum(result.score)\n" +
                "                       END AS 其他\n" +
                "            FROM (\n" +
                "                     SELECT levelTwo.c_dept_id,\n" +
                "                            levelTwo.c_dept_name,\n" +
                "                            levelTwo.oneRuleId,\n" +
                "                            levelTwo.oneContent,\n" +
                "                            IF\n" +
                "                                (\n" +
                "                                    MAX(levelTwo.itemScore) = - 1.0, - sum(levelTwo.twoDockedScore),\n" +
                "                                    IF\n" +
                "                                        (\n" +
                "                                            max(levelTwo.itemScore) - sum(levelTwo.twoDockedScore) > 0,\n" +
                "                                            max(levelTwo.itemScore) - sum(levelTwo.twoDockedScore),\n" +
                "                                            0\n" +
                "                                        )\n" +
                "                                )                        AS score,\n" +
                "                            sum(levelTwo.twoDockedScore) AS dockedScore\n" +
                "                     FROM (\n" +
                "                              SELECT log.c_dept_id,\n" +
                "                                     log.c_dept_name,\n" +
                "                                     max(rule.itemScore)  AS itemScore,\n" +
                "                                     rule.oneRuleId,\n" +
                "                                     rule.oneContent,\n" +
                "                                     rule.twoRuleId,\n" +
                "                                     rule.twoContent,\n" +
                "                                     max(rule.totalScore) AS totalScore,\n" +
                "                                     sum(log.dockedScore) AS twoDockedScore\n" +
                "                              FROM (\n" +
                "                                       SELECT t1.c_rule_id     AS oneRuleId,\n" +
                "                                              t1.c_content     AS oneContent,\n" +
                "                                              t1.c_item_score  AS itemScore,\n" +
                "                                              t1.c_order_num   AS oneOrder,\n" +
                "                                              t2.c_order_num   AS twoOrder,\n" +
                "                                              t2.c_rule_id     AS twoRuleId,\n" +
                "                                              t2.c_content     AS twoContent,\n" +
                "                                              t2.c_total_score AS totalScore,\n" +
                "                                              t3.c_rule_id     AS threeRuleId,\n" +
                "                                              t3.c_content     AS threeContent\n" +
                "                                       FROM t_evaluation_rules t1\n" +
                "                                                INNER JOIN t_evaluation_rules t2 ON t1.c_rule_id = t2.c_rule_pid\n" +
                "                                           AND t1.c_layer = 0\n" +
                "                                           AND t1.c_db_status = 0\n" +
                "                                           AND t2.c_db_status = 0\n" +
                "                                                INNER JOIN t_evaluation_rules t3 ON t2.c_rule_id = t3.c_rule_pid\n" +
                "                                           AND t3.c_db_status = 0\n" +
                "                                   ) rule\n" +
                "                                       LEFT JOIN (\n" +
                "                                  SELECT score.c_rule_id,\n" +
                "                                         score.c_dept_id,\n" +
                "                                         score.c_dept_name,\n" +
                "                                         sum(\n" +
                "                                                 IFNULL(score.c_dock_score, 0)) AS dockedScore\n" +
                "                                  FROM t_evaluation_log score\n" +
                "                                  WHERE score.c_db_status = 0\n" +
                "                                    AND score.c_log_date between  "+ map.get("beginDate").toString()+" and "
                + map.get("endDate").toString() +
                "                                  GROUP BY score.c_rule_id,\n" +
                "                                           score.c_dept_id,\n" +
                "                                           score.c_dept_name\n" +
                "                              ) log ON rule.threeRuleId = log.c_rule_id\n" +
                "                              GROUP BY log.c_dept_id,\n" +
                "                                       log.c_dept_name,\n" +
                "                                       rule.oneRuleId,\n" +
                "                                       rule.oneContent,\n" +
                "                                       rule.twoRuleId,\n" +
                "                                       rule.twoContent\n" +
                "                          ) AS levelTwo\n" +
                "                     GROUP BY levelTwo.c_dept_id,\n" +
                "                              levelTwo.c_dept_name,\n" +
                "                              levelTwo.oneRuleId,\n" +
                "                              levelTwo.oneContent\n" +
                "                     HAVING levelTwo.c_dept_id IS NOT NULL\n" +
                "                 ) AS result\n" +
                "            GROUP BY result.c_dept_id,\n" +
                "                     result.c_dept_name,\n" +
                "                     result.oneRuleId,\n" +
                "                     result.oneContent\n" +
                "        ) ttt ON dept.c_dept_id = ttt.c_dept_id\n" +
                "        GROUP BY dept.c_dept_id,\n" +
                "                 dept.c_dept_name");
    }

    @Override
    public List<Map<String, Object>> getTotalScoreList() {
       return getMap("SELECT max(t1.c_content) as content,\n" +
                "               max(t1.c_rule_id) as ruleId,\n" +
                "               IF(max(t1.c_content) = '其他', 0, sum(t2.c_total_score)) as totalScore\n" +
                "        FROM t_evaluation_rules t1\n" +
                "                 INNER JOIN t_evaluation_rules t2 ON t1.c_rule_id = t2.c_rule_pid\n" +
                "            AND t1.c_layer = 0\n" +
                "            AND t1.c_db_status = 0\n" +
                "            AND t2.c_db_status = 0\n" +
                "        group by t1.c_rule_id");
    }

    @Override
    public List<Map<String, Object>> getStatisticsFormalTableByTableId(Integer tableId) {
      return getMap("SELECT dept.c_dept_id                                AS deptId,\n" +
               "               dept.c_dept_name                              AS deptName,\n" +
               "               100 - (\n" +
               "                   IFNULL(sum(ttt.dockedScore), 0))          AS score,\n" +
               "               sum(\n" +
               "                       CAST(\n" +
               "                               ttt.日常养护记录 AS DECIMAL(3, 1))) AS 日常养护记录,\n" +
               "               sum(\n" +
               "                       CAST(\n" +
               "                               ttt.安全生产 AS DECIMAL(3, 1)))   AS 安全生产,\n" +
               "               sum(\n" +
               "                       CAST(\n" +
               "                               ttt.卫生保洁 AS DECIMAL(3, 1)))   AS 卫生保洁,\n" +
               "               sum(\n" +
               "                       CAST(\n" +
               "                               ttt.园林管理 AS DECIMAL(3, 1)))   AS 园林管理,\n" +
               "               sum(\n" +
               "                       CAST(\n" +
               "                               ttt.绿化养护 AS DECIMAL(3, 1)))   AS 绿化养护,\n" +
               "               sum(\n" +
               "                       CAST(\n" +
               "                               ttt.日常施肥 AS DECIMAL(3, 1)))   AS 日常施肥,\n" +
               "               sum(\n" +
               "                       CAST(\n" +
               "                               ttt.日常巡查 AS DECIMAL(3, 1)))   AS 日常巡查,\n" +
               "               sum(\n" +
               "                       CAST(\n" +
               "                               ttt.侧重性检查 AS DECIMAL(3, 1)))  AS 侧重性检查,\n" +
               "               sum(\n" +
               "                       CAST(\n" +
               "                               ttt.其他 AS DECIMAL(3, 1)))     AS 其他\n" +
               "        FROM (\n" +
               "                 SELECT b.*\n" +
               "                 FROM t_sys_dept_in_tree a\n" +
               "                          JOIN t_sys_dept b ON a.c_dept_id = b.c_dept_id\n" +
               "                     AND a.c_db_status = 0\n" +
               "                     AND b.c_dept_type = '15003'\n" +
               "                 WHERE c_dept_tree_id = '55003f98-f416-4dbc-b049-513337bbc4c6'\n" +
               "             ) AS dept\n" +
               "                 LEFT JOIN (\n" +
               "            SELECT result.c_dept_id,\n" +
               "                   result.c_dept_name,\n" +
               "                   result.oneRuleId,\n" +
               "                   result.oneContent,\n" +
               "                   result.dockedScore,\n" +
               "                   CASE\n" +
               "                       oneContent\n" +
               "                       WHEN '日常养护记录' THEN\n" +
               "                           sum(result.score)\n" +
               "                       END AS 日常养护记录,\n" +
               "                   CASE\n" +
               "                       oneContent\n" +
               "                       WHEN '安全生产' THEN\n" +
               "                           sum(result.score)\n" +
               "                       END AS 安全生产,\n" +
               "                   CASE\n" +
               "                       oneContent\n" +
               "                       WHEN '卫生保洁' THEN\n" +
               "                           sum(result.score)\n" +
               "                       END AS 卫生保洁,\n" +
               "                   CASE\n" +
               "                       oneContent\n" +
               "                       WHEN '园林管理' THEN\n" +
               "                           sum(result.score)\n" +
               "                       END AS 园林管理,\n" +
               "                   CASE\n" +
               "                       oneContent\n" +
               "                       WHEN '绿化养护' THEN\n" +
               "                           sum(result.score)\n" +
               "                       END AS 绿化养护,\n" +
               "                   CASE\n" +
               "                       oneContent\n" +
               "                       WHEN '日常施肥' THEN\n" +
               "                           sum(result.score)\n" +
               "                       END AS 日常施肥,\n" +
               "                   CASE\n" +
               "                       oneContent\n" +
               "                       WHEN '日常巡查' THEN\n" +
               "                           sum(result.score)\n" +
               "                       END AS 日常巡查,\n" +
               "                   CASE\n" +
               "                       oneContent\n" +
               "                       WHEN '侧重性检查' THEN\n" +
               "                           sum(result.score)\n" +
               "                       END AS 侧重性检查,\n" +
               "                   CASE\n" +
               "                       oneContent\n" +
               "                       WHEN '其他' THEN\n" +
               "                           sum(result.score)\n" +
               "                       END AS 其他\n" +
               "            FROM (\n" +
               "                     SELECT levelTwo.c_dept_id,\n" +
               "                            levelTwo.c_dept_name,\n" +
               "                            levelTwo.oneRuleId,\n" +
               "                            levelTwo.oneContent,\n" +
               "                            IF\n" +
               "                                (\n" +
               "                                    MAX(levelTwo.itemScore) = - 1.0, - sum(levelTwo.twoDockedScore),\n" +
               "                                    IF\n" +
               "                                        (\n" +
               "                                            max(levelTwo.itemScore) - sum(levelTwo.twoDockedScore) > 0,\n" +
               "                                            max(levelTwo.itemScore) - sum(levelTwo.twoDockedScore),\n" +
               "                                            0\n" +
               "                                        )\n" +
               "                                )                        AS score,\n" +
               "                            sum(levelTwo.twoDockedScore) AS dockedScore\n" +
               "                     FROM (\n" +
               "                              SELECT log.c_dept_id,\n" +
               "                                     log.c_dept_name,\n" +
               "                                     max(rule.itemScore)  AS itemScore,\n" +
               "                                     rule.oneRuleId,\n" +
               "                                     rule.oneContent,\n" +
               "                                     rule.twoRuleId,\n" +
               "                                     rule.twoContent,\n" +
               "                                     max(rule.totalScore) AS totalScore,\n" +
               "                                     sum(log.dockedScore) AS twoDockedScore\n" +
               "                              FROM (\n" +
               "                                       SELECT t1.c_rule_id     AS oneRuleId,\n" +
               "                                              t1.c_content     AS oneContent,\n" +
               "                                              t1.c_item_score  AS itemScore,\n" +
               "                                              t1.c_order_num   AS oneOrder,\n" +
               "                                              t2.c_order_num   AS twoOrder,\n" +
               "                                              t2.c_rule_id     AS twoRuleId,\n" +
               "                                              t2.c_content     AS twoContent,\n" +
               "                                              t2.c_total_score AS totalScore,\n" +
               "                                              t3.c_rule_id     AS threeRuleId,\n" +
               "                                              t3.c_content     AS threeContent\n" +
               "                                       FROM t_evaluation_rules t1\n" +
               "                                                INNER JOIN t_evaluation_rules t2 ON t1.c_rule_id = t2.c_rule_pid\n" +
               "                                           AND t1.c_layer = 0\n" +
               "                                           AND t1.c_db_status = 0\n" +
               "                                           AND t2.c_db_status = 0\n" +
               "                                                INNER JOIN t_evaluation_rules t3 ON t2.c_rule_id = t3.c_rule_pid\n" +
               "                                           AND t3.c_db_status = 0\n" +
               "                                   ) rule\n" +
               "                                       LEFT JOIN (\n" +
               "                                  SELECT score.c_rule_id,\n" +
               "                                         score.c_dept_id,\n" +
               "                                         score.c_dept_name,\n" +
               "                                         sum(\n" +
               "                                                 IFNULL(score.c_dock_score, 0)) AS dockedScore\n" +
               "                                  FROM t_evaluation_log_formal score\n" +
               "                                  WHERE score.c_db_status = 0\n" +
               "                                    AND score.c_report_table_id = '"+tableId+"'" +
               "                                  GROUP BY score.c_rule_id,\n" +
               "                                           score.c_dept_id,\n" +
               "                                           score.c_dept_name\n" +
               "                              ) log ON rule.threeRuleId = log.c_rule_id\n" +
               "                              GROUP BY log.c_dept_id,\n" +
               "                                       log.c_dept_name,\n" +
               "                                       rule.oneRuleId,\n" +
               "                                       rule.oneContent,\n" +
               "                                       rule.twoRuleId,\n" +
               "                                       rule.twoContent\n" +
               "                          ) AS levelTwo\n" +
               "                     GROUP BY levelTwo.c_dept_id,\n" +
               "                              levelTwo.c_dept_name,\n" +
               "                              levelTwo.oneRuleId,\n" +
               "                              levelTwo.oneContent\n" +
               "                     HAVING levelTwo.c_dept_id IS NOT NULL\n" +
               "                 ) AS result\n" +
               "            GROUP BY result.c_dept_id,\n" +
               "                     result.c_dept_name,\n" +
               "                     result.oneRuleId,\n" +
               "                     result.oneContent\n" +
               "        ) ttt ON dept.c_dept_id = ttt.c_dept_id\n" +
               "        GROUP BY dept.c_dept_id,\n" +
               "                 dept.c_dept_name");
    }

    @Override
    public List<EvaluationTableVO> getReportedEvaluationTable(Map<String, Object> map) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT tt.oneRuleId,\n" +
                "        tt.oneContent,\n" +
                "        tt.twoRuleId,\n" +
                "        tt.twoContent,\n" +
                "        tt.totalScore,\n" +
                "        tt.oneOrder,\n" +
                "        tt.twoOrder,\n" +
                "        t3.c_order_num as threeOrder,\n" +
                "        t3.c_rule_id threeRuleId,\n" +
                "        t3.c_content threeContent,\n" +
                "        t3.c_dock_score AS dockScore,\n" +
                "        t3.c_unit AS unit,\n" +
                "        IFNULL(ss.dockedScore, 0) dockedScore\n" +
                "        FROM (\n" +
                "        SELECT t1.c_rule_id AS oneRuleId,\n" +
                "        t1.c_content AS oneContent,\n" +
                "        t1.c_order_num as oneOrder,\n" +
                "        t2.c_order_num as twoOrder,\n" +
                "        t2.c_rule_id AS twoRuleId,\n" +
                "        t2.c_content AS twoContent,\n" +
                "        t2.c_total_score AS totalScore\n" +
                "        FROM t_evaluation_rules t1\n" +
                "        INNER JOIN t_evaluation_rules t2 ON t1.c_rule_id = t2.c_rule_pid\n" +
                "        AND t1.c_layer = 0\n" +
                "        AND t1.c_db_status = 0\n");
                if(map.get("ruleId").toString() != null && map.get("ruleId").toString() != ""){
                    sb.append(" AND t1.c_rule_id ="+map.get("ruleId").toString());
                }
                sb.append(
                "        AND t2.c_db_status = 0\n" +
                "        ) tt\n" +
                "        INNER JOIN t_evaluation_rules t3 ON tt.twoRuleId = t3.c_rule_pid\n" +
                "        AND t3.c_db_status = 0\n" +
                "        LEFT JOIN (\n" +
                "        SELECT score.c_rule_id,\n" +
                "        score.c_dept_id,\n" +
                "        sum(IFNULL(score.c_dock_score, 0)) as dockedScore\n" +
                "        FROM t_evaluation_log_formal score\n" +
                "        WHERE score.c_db_status = 0\n" +
                "        and score.c_report_table_id = " +map.get("tableId").toString()+
                "        GROUP BY score.c_rule_id,\n" +
                "        score.c_dept_id\n" +
                "        ) ss ON t3.c_rule_id = ss.c_rule_id");

        AtomicReference<List<EvaluationTableVO>> vo = new AtomicReference<>(new ArrayList<>());
        run(sb.toString(), CommandType.Text, dbCommand -> {
            vo.set(dbCommand.executeToList(EvaluationTableVO.class));
        });

        return vo.get();
    }

    @Override
    public List<EvaluationTreeVO> getEvaluationTreeForMobile(Map<String, String> map) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT rule.c_rule_id                   as ruleId,\n" +
                "               rule.c_content                   as ruleName,\n" +
                "               sum(IFNULL(log.c_dock_score, 0)) as dockedScore\n" +
                "        from t_evaluation_rules rule\n" +
                "                 LEFT JOIN t_evaluation_log log ON rule.c_rule_id = log.c_rule_id and log.c_log_date >= #{map.beginDate}\n" +
                "            and log.c_log_date <= '" +map.get("endDate").toString()+"'"+
                "            and log.c_dept_id = '" +map.get("deptId").toString()+"'"+
                "        WHERE rule.c_rule_pid = '" +map.get("rulePid").toString()+"'"+
                "        GROUP BY rule.c_rule_id,\n" +
                "                 rule.c_content");

        AtomicReference<List<EvaluationTreeVO>> vo = new AtomicReference<>(new ArrayList<>());
        run(sb.toString(), CommandType.Text, dbCommand -> {
            vo.set(dbCommand.executeToList(EvaluationTreeVO.class));
        });

        return vo.get();
    }
}
